/***********************************************************************************************************************************
 * Name:	DBs_Search-Procedure.sql
 * Author:	Frank Figearo (frank@sqlnerd.me)
 * Summary:	Search all DBs for a procedure or function by name.
 */
SET IMPLICIT_TRANSACTIONS OFF; WHILE 0 < @@TRANCOUNT ROLLBACK;
GO
DECLARE
  @search_string	SYSNAME,
  @tsql_template	NVARCHAR(MAX),
  @tsql_command		NVARCHAR(MAX);
SET @search_string= '%%';
CREATE TABLE #procedures (ServerName SYSNAME NOT Null, DBName SYSNAME NOT Null, ProcedureName SYSNAME NOT Null, ProcedureType NVARCHAR(32) NOT Null);
SET @tsql_template= N'INSERT INTO #procedures SELECT @@SERVERNAME, ''?'', name, type_desc FROM [?].sys.procedures WHERE name LIKE @search_name';
DECLARE list CURSOR LOCAL FAST_FORWARD FOR SELECT REPLACE(@tsql_template, '?', name) FROM sys.databases WHERE 4 < database_id ORDER BY name;
OPEN list;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM list INTO @tsql_command;
  IF @@FETCH_STATUS <> 0 BREAK;
  EXECUTE sp_executesql @tsql_command, N'@search_name SYSNAME', @search_string;
END;
CLOSE list;
DEALLOCATE list;
SELECT * FROM #procedures
DROP TABLE #procedures;